--    Program: 		CAPARIMP_SALESPERSON.sql
--    Description:  Import AR Salesperson information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_salesrep_id			number;
                    		                 		
   cursor cur_sales is
      select * from CRST_JTF_RS_SALESREPS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_sales loop   	
     l_err_msg_tmp := ''; 
     
     select RA_SALESREPS_S.NEXTVAL
     into   l_salesrep_id
     from   dual;
     
     update CRST_JTF_RS_SALESREPS
     set    salesrep_id_n = l_salesrep_id,
            org_id_n = CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
     where  salesrep_id = cur.SALESREP_ID
     and    org_id = cur.ORG_ID;       
   	 
   	 begin
       -- import data
       insert into JTF_RS_SALESREPS
       (
         SALESREP_ID                   ,                                                                                               
         RESOURCE_ID                   ,                                                                                               
         LAST_UPDATE_DATE              ,                                                                                               
         LAST_UPDATED_BY               ,                                                                                               
         CREATION_DATE                 ,                                                                                               
         CREATED_BY                    ,                                                                                               
         LAST_UPDATE_LOGIN             ,                                                                                               
         SALES_CREDIT_TYPE_ID          ,                                                                                               
         NAME                          ,                                                                                               
         STATUS                        ,                                                                                               
         START_DATE_ACTIVE             ,                                                                                               
         END_DATE_ACTIVE               ,                                                                                               
         GL_ID_REV                     ,                                                                                               
         GL_ID_FREIGHT                 ,                                                                                               
         GL_ID_REC                     ,                                                                                               
         SET_OF_BOOKS_ID               ,                                                                                               
         SALESREP_NUMBER               ,                                                                                               
         ORG_ID                        ,
         EMAIL_ADDRESS                 ,                                                                                               
         WH_UPDATE_DATE                ,                                                                                               
         PERSON_ID                     ,                                                                                               
         SALES_TAX_GEOCODE             ,                                                                                               
         SALES_TAX_INSIDE_CITY_LIMITS  ,                                                                                               
         OBJECT_VERSION_NUMBER         ,                                                                                               
         ATTRIBUTE_CATEGORY            ,                                                                                               
         ATTRIBUTE1                    ,                                                                                               
         ATTRIBUTE2                    ,                                                                                               
         ATTRIBUTE3                    ,                                                                                               
         ATTRIBUTE4                    ,                                                                                               
         ATTRIBUTE5                    ,                                                                                               
         ATTRIBUTE6                    ,                                                                                               
         ATTRIBUTE7                    ,                                                                                               
         ATTRIBUTE8                    ,                                                                                               
         ATTRIBUTE9                    ,                                                                                               
         ATTRIBUTE10                   ,                                                                                               
         ATTRIBUTE11                   ,                                                                                               
         ATTRIBUTE12                   ,                                                                                               
         ATTRIBUTE13                   ,                                                                                               
         ATTRIBUTE14                   ,                                                                                               
         ATTRIBUTE15                   ,                                                                                               
         SECURITY_GROUP_ID
       )
       values
       (
         l_salesrep_id                     ,                                                                                               
         cur.RESOURCE_ID                   ,                                                                                               
         cur.LAST_UPDATE_DATE              ,                                                                                               
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),                                                                                           
         cur.CREATION_DATE                 ,                                                                                               
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),                                                                                              
         cur.LAST_UPDATE_LOGIN             ,                                                                                               
         cur.SALES_CREDIT_TYPE_ID          ,                                                                                               
         cur.NAME                          ,                                                                                               
         cur.STATUS                        ,                                                                                               
         cur.START_DATE_ACTIVE             ,                                                                                               
         cur.END_DATE_ACTIVE               ,                                                                                               
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REV),                                                                                               
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_FREIGHT),                                                                                               
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REC),                                                                                               
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),                                                                                               
         cur.SALESREP_NUMBER               ,                                                                                               
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.EMAIL_ADDRESS                 ,                                                                                               
         cur.WH_UPDATE_DATE                ,                                                                                               
         cur.PERSON_ID                     ,                                                                                               
         cur.SALES_TAX_GEOCODE             ,                                                                                               
         cur.SALES_TAX_INSIDE_CITY_LIMITS  ,                                                                                               
         cur.OBJECT_VERSION_NUMBER         ,                                                                                               
         cur.ATTRIBUTE_CATEGORY            ,                                                                                               
         cur.ATTRIBUTE1                    ,                                                                                               
         cur.ATTRIBUTE2                    ,                                                                                               
         cur.ATTRIBUTE3                    ,                                                                                               
         cur.ATTRIBUTE4                    ,                                                                                               
         cur.ATTRIBUTE5                    ,                                                                                               
         cur.ATTRIBUTE6                    ,                                                                                               
         cur.ATTRIBUTE7                    ,                                                                                               
         cur.ATTRIBUTE8                    ,                                                                                               
         cur.ATTRIBUTE9                    ,                                                                                               
         cur.ATTRIBUTE10                   ,                                                                                               
         cur.ATTRIBUTE11                   ,                                                                                               
         cur.ATTRIBUTE12                   ,                                                                                               
         cur.ATTRIBUTE13                   ,                                                                                               
         cur.ATTRIBUTE14                   ,                                                                                               
         cur.ATTRIBUTE15                   ,                                                                                               
         cur.SECURITY_GROUP_ID
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_SALESPERSON', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit